package com.junjie.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.junjie.dao.ICategoryDao;
import com.junjie.entity.Category;
import com.junjie.util.ConfigUtil;
import com.junjie.util.DBUtil;

public class CategoryDaoImpl implements ICategoryDao{

	/**
	 * 查询全部分类
	 */
	
	public List<Category> select(String sql,Object[] parameter) throws Exception {
		List<Category> categoryList = new ArrayList<Category>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			if(parameter != null && parameter.length >0){
				for (int i = 1; i <= parameter.length; i++) {
					pstmt.setObject(i, parameter[i-1]);
				}
			}
			rs = pstmt.executeQuery();
			while(rs.next()){
				Category category = new Category();
				category.setId(rs.getInt("id"));
				category.setEnName(rs.getString("en_name"));
				category.setName(rs.getString("name"));
				category.setTurn(rs.getInt("turn"));
				category.setParentId(rs.getInt("parent_id"));
				category.setDescription(rs.getString("description"));
				categoryList.add(category);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs,pstmt,conn);
		}
			
		return categoryList;
	}
	/**
	 * 显示分类列表(重新组装页面显示信息)
	 * @throws Exception 
	 */
	public List<Category> showCategory() throws Exception{
		List<Category> categoryList = select(ConfigUtil.getValue("FIND_ALL_CATEGORY"), null);
		//获取一级标题
		List<Category> categorys = subCategory(categoryList, 1); 
		//获取一级标题ID
		for (Category category : categorys) {
			List<Category> subCategorys = subCategory(categoryList, category.getId());
			//在Category类中添加一个二级标题属性
			category.setSubCategorys(subCategorys);
		}
		return categorys;
		
	}
	/**
	 * 获取子标题
	 */
	public List<Category> subCategory(List<Category> categoryList,int id){
		List<Category> subCategorys = new ArrayList<Category>();
		for (Category category : categoryList) {
			if(category.getParentId()==id){
				subCategorys.add(category);
			}
		}
		return subCategorys;
	}
	/**
	 * 根据parend_id 显示分类
	 * @throws Exception 
	 */
	public List<Category> findCategoryByParendId(Category category) throws Exception{
		
		return select(ConfigUtil.getValue("FIND_BY_PARENTID"),new Object[]{category.getParentId()});
		
	}
	
	/**
	 *根据id获取当前分类的名字
	 */
	public Category findCategoryName(Category category)throws Exception{
		List<Category> categorys = select(ConfigUtil.getValue("FIND_NAME"),new Object[]{category.getParentId()});
		if(categorys.size()>0){
			return categorys.get(0);
		}else{
			return null;
		}
	}
	/**
	 * 根据父类项id查询分类浏览
	 * @throws Exception 
	 */
	public List<Category> findCategoryByParentId(Category category) throws Exception{
		 List<Category>  categorys =new ArrayList<Category>();
		 Connection conn =null;
		 PreparedStatement ps = null;
		 ResultSet rs = null;
		 try{
			 conn=DBUtil.getConnection();
			 String sql = "select c.name,c.id,c.parent_id,count(cp.product_id) count_product from d_category c left join  d_category_product cp  on c.id=cp.cat_id where c.parent_id= ? group by c.name,c.id,c.parent_id";
			 ps=conn.prepareStatement(sql);
			 ps.setInt(1,category.getParentId());
			 rs=ps.executeQuery();
			 while(rs.next()){
				 Category cate = new Category();
				 cate.setId(rs.getInt("id"));
				 cate.setName(rs.getString("name"));
				 cate.setParentId(rs.getInt("parent_id"));
				 //在Category添加一个countProduct
				 cate.setCountProduct((rs.getInt("count_product")));
				 categorys.add(cate);
			 }
		 }finally{
			 DBUtil.close(rs, ps, conn);
		 }
		 return categorys;
	}
	
	
	/**
	 * 测试
	 */
	public static void main(String[] args) throws Exception {
		Category category = new Category();
		category.setId(2);
		System.out.println(new CategoryDaoImpl().findCategoryName(category).getName());
	}
}
